/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.surveilans.model;

/**
 *
 * @author Yudi Retanto
 */
import com.surveilans.classes.InfoSurveilans;
import com.surveilans.classes.InfoSurveilansHari;
import com.surveilans.classes.Penyakit;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.nio.charset.Charset;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;

public class InfoModel extends Model {

    public InfoModel() {
        super();
    }

    public InfoSurveilans getInfo(int penyakit, int kabupaten, int tahun, int bulan) {
        InfoSurveilans is = new InfoSurveilans();
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String bawah = tahun + "-" + bulan + "-1";
            String atas = tahun + "-" + bulan + "-31";
            String query = "SELECT *,SUM(range1) AS r1,"
                    + "SUM(range2) AS r2,"
                    + "SUM(range3) AS r3,"
                    + "SUM(range4) AS r4,"
                    + "SUM(range5) AS r5,"
                    + "SUM(range6) AS r6,"
                    + "SUM(range7) AS r7,"
                    + "SUM(range8) AS r8,"
                    + "SUM(range9) AS r9,"
                    + "SUM(range10) AS r10,"
                    + "SUM(range11) AS r11,"
                    + "SUM(range12) AS r12 "
                    + "FROM data_surveilans "
                    + "LEFT JOIN unit ON data_surveilans.id_unit=unit.id "
                    + "WHERE tanggal BETWEEN '" + bawah + "' AND '" + atas + "' "
                    + "AND unit.kabupaten=" + kabupaten + " "
                    + "AND id_penyakit=" + penyakit + " "
                    + "GROUP BY tanggal";
            ResultSet rs = stmt.executeQuery(query);

            is.setBulan(bulan);
            is.setPenyakit_str(this.getNamaPenyakit(penyakit));
            is.setKabupaten_str(this.getNamaKabupaten(kabupaten));

            while (rs.next()) {
                DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");
                Date a = null;
                try {
                    a = dfm.parse(rs.getString("tanggal"));
                } catch (ParseException ex) {
                    Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
                }
                int total = rs.getInt("r1") + rs.getInt("r2") + rs.getInt("r3") + rs.getInt("r4") + rs.getInt("r5") + rs.getInt("r6") + rs.getInt("r7") + rs.getInt("r8") + rs.getInt("r9") + rs.getInt("r10") + rs.getInt("r11") + rs.getInt("r12");
                is.setPenderita(a.getDate() - 1, total);
            }
        } catch (SQLException ex) {
            Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return is;
    }
    
    public InfoSurveilansHari getInfo(int penyakit, int kabupaten, int tahun, int bulan, int hari) {
        InfoSurveilansHari is = new InfoSurveilansHari();
        Statement stmt = null;
        NumberFormat nf = NumberFormat.getInstance();
        nf.setMinimumIntegerDigits(2);
        String tanggal = tahun+"-"+nf.format(bulan) +"-"+nf.format(hari);
        try {
            stmt = con.createStatement();
            String query = "SELECT *,SUM(range1) AS r1,"
                    + "SUM(range2) AS r2,"
                    + "SUM(range3) AS r3,"
                    + "SUM(range4) AS r4,"
                    + "SUM(range5) AS r5,"
                    + "SUM(range6) AS r6,"
                    + "SUM(range7) AS r7,"
                    + "SUM(range8) AS r8,"
                    + "SUM(range9) AS r9,"
                    + "SUM(range10) AS r10,"
                    + "SUM(range11) AS r11,"
                    + "SUM(range12) AS r12 "
                    + "FROM data_surveilans "
                    + "LEFT JOIN unit ON data_surveilans.id_unit=unit.id "
                    + "WHERE tanggal='"+tanggal+"' "
                    + "AND unit.kabupaten=" + kabupaten + " "
                    + "AND id_penyakit=" + penyakit + " "
                    + "GROUP BY tanggal";
            ResultSet rs = stmt.executeQuery(query);

            SimpleDateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");
            Date d = null;
            try {
                d = dfm.parse(tanggal);
            } catch (ParseException ex) {
                Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            is.setTanggal(d);
            is.setPenyakit_str(this.getNamaPenyakit(penyakit));
            is.setKabupaten_str(this.getNamaKabupaten(kabupaten));
            
            if(rs.next()){
                is.setRange(0, rs.getInt("r1"));
                is.setRange(1, rs.getInt("r2"));
                is.setRange(2, rs.getInt("r3"));
                is.setRange(3, rs.getInt("r4"));
                is.setRange(4, rs.getInt("r5"));
                is.setRange(5, rs.getInt("r6"));
                is.setRange(6, rs.getInt("r7"));
                is.setRange(7, rs.getInt("r8"));
                is.setRange(8, rs.getInt("r9"));
                is.setRange(9, rs.getInt("r10"));
                is.setRange(10, rs.getInt("r11"));
                is.setRange(11, rs.getInt("r12"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return is;
    }

    public InfoSurveilans getInfoProvinsi(int penyakit, int provinsi, int tahun, int bulan) {
        InfoSurveilans is = new InfoSurveilans();
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String bawah = tahun + "-" + bulan + "-1";
            String atas = tahun + "-" + bulan + "-31";
            String query = "SELECT *,SUM(range1) AS r1,"
                    + "SUM(range2) AS r2,"
                    + "SUM(range3) AS r3,"
                    + "SUM(range4) AS r4,"
                    + "SUM(range5) AS r5,"
                    + "SUM(range6) AS r6,"
                    + "SUM(range7) AS r7,"
                    + "SUM(range8) AS r8,"
                    + "SUM(range9) AS r9,"
                    + "SUM(range10) AS r10,"
                    + "SUM(range11) AS r11,"
                    + "SUM(range12) AS r12 "
                    + "FROM data_surveilans "
                    + "LEFT JOIN unit ON data_surveilans.id_unit=unit.id "
                    + "WHERE tanggal BETWEEN '" + bawah + "' AND '" + atas + "' "
                    + "AND id_penyakit=" + penyakit + " "
                    + "AND unit.kabupaten IN "
                    + "(SELECT id FROM kabupaten WHERE id_provinsi=" + provinsi + ") "
                    + "GROUP BY tanggal";
            ResultSet rs = stmt.executeQuery(query);

            is.setBulan(bulan);
            is.setPenyakit_str(this.getNamaPenyakit(penyakit));
            is.setProvinsi_str(this.getNamaProvinsi(provinsi));
            
            DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");
            while (rs.next()) {
                
                Date a = null;
                try {
                    a = dfm.parse(rs.getString("tanggal"));
                } catch (ParseException ex) {
                    Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
                }
                int total = rs.getInt("r1") + rs.getInt("r2") + rs.getInt("r3") + rs.getInt("r4") + rs.getInt("r5") + rs.getInt("r6") + rs.getInt("r7") + rs.getInt("r8") + rs.getInt("r9") + rs.getInt("r10") + rs.getInt("r11") + rs.getInt("r12");
                is.setPenderita(a.getDate() - 1, total);
            }
        } catch (SQLException ex) {
            Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return is;
    }

    public String getNamaPenyakit(int id_penyakit) {
        String result = "";
        Statement stmt = null;
        try {
            stmt = con.createStatement();

            String query = "SELECT nama FROM penyakit WHERE id=" + id_penyakit;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                result = rs.getString("nama");
            }
        } catch (SQLException ex) {
            Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public String getNamaKabupaten(int id_kabupaten) {
        String result = "";
        Statement stmt = null;
        try {
            stmt = con.createStatement();

            String query = "SELECT nama FROM kabupaten WHERE id=" + id_kabupaten;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                result = rs.getString("nama");
            }
        } catch (SQLException ex) {
            Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public String getNamaProvinsi(int id_provinsi) {
        String result = "";
        Statement stmt = null;
        try {
            stmt = con.createStatement();

            String query = "SELECT nama FROM provinsi WHERE id=" + id_provinsi;
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                result = rs.getString("nama");
            }
        } catch (SQLException ex) {
            Logger.getLogger(InfoModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    // FILE CSV GENERATOR
    
    public void createDataCSV(String path) {
        try {
            
            Writer out = new OutputStreamWriter(new FileOutputStream(path), Charset.forName("ISO-8859-1"));
            // HEADER
            out.write("LOCALE,CATEGORY,YEAR,INDICATOR,SOURCE,DESCRIPTION,UNIT,MAP,GRAPH,SYMBOL,OPTIONS,TYPE,NAD,SUMUT,SUMBAR,RIAU,JAMBI,SUMSEL,BENGKULU,LAMPUNG,KEP. BABEL,KEP. RIAU,DKI JAKARTA,JABAR,JATENG,DIY,JATIM,BANTEN,BALI,NTB,NTT,KALBAR,KALTENG,KALSEL,KALTIM,SULUT,SULTENG,SULSEL,SULTRA,GORONTALO,SULBAR,MALUKU,MALUT,PAPBAR,PAPUA,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
            out.write("SETTINGS,,,,DBF-ID,REGION,GRAPH,BOOKM,REGION-L,REGION-S,V-REGION,V-ICONS,V-I-VIEW,V-I-GRAPH,V-I-SYMB,V-I-OPTION,V-I-TABLE,V-I-SELECT,V-I-FULLSCR,V-I-STAR,V-STAT,V-TIME,FULLSCR,MAP-TXT-ALL,VIEW0,VIEW1,VIEW2,VIEW3,VIEW4,VIEW5,VIEW6,VIEW7,VIEW8,VIEW9,VIEW10,VIEW11,VIEW12,VIEW13,VIEW14,VIEW15,VIEW16,MAP-NAV,MAP-BG-P,MAP-GR,MAP-TXT-S,MAP-TXT-S2,MAP-SYMB-S,MAP-SYMB-TR,LEGEND-TR,MAP-NAV-TR,LEGEND-EST,LEGEND-VAL,MAP-CLR0,MAP-CLR1,MAP-CLR2,MAP-CLR3,MAP-CLR4,MAP-CLR5,MAP-CLR6,MAP-CLR7,MAP-CLR8,MAP-BG-I,MAP-BG-O,MAP-BRDR,MAP-NTRL,MAP-ND,MAP-OVER,MAP-LINE-CLR,MAP-S-CLR");
            out.write(",MAP-S-BRDR-CLR,MAP-TXT-C1,MAP-TXT-C2,GR-TXT-S,GR-AXIS,GR-BRDR-T,GR-WIDTH,GR-HEIGHT,GR-GRID-T,GR-MARGIN,GR-DOT-S,GR-TRENDL,GR-SORT,GR-SORTL,GR-A-TR,GR-A-CLR,GR-BUL-CLR,GR-BG-CLR,GR-BRDR-CLR,GR-P-BRDR-CLR,GR-GRID-CLR,WINSCALE,IND-MAX,POPUP-C,BRDR-THCK,WINTOP-HEIGHT,GUI-TOP-TR1,GUI-TOP-TR2,FONT-S,FONT-P-S,FONT-C2-TH,FONT-C1,FONT-C2,GUI-BODY-CLR,GUI-BODY2-CLR,GUI-BODY3-CLR,GUI-TOP-CLR,GUI-BRDR-CLR,BTN-CLR1,BTN-CLR2,BTN-CLR3,GUI-CLR-H1a,GUI-CLR-H1b,GUI-CLR-H2a,GUI-CLR-H2b,POPUP-CLR1,POPUP-CLR2,BTN-CLR-B1,BTN-CLR-B2,BTN-CLR-B3,BTN-CLR-T,BTN-CLR-S,ANI-TIME,DECPLACES,ADJSCALE1,ADJSCALE2,ADJSCALE3,VISTYPE,T_GR-OPT,T_GR-SORT,T_GR-SORTL,T_GR-SORT1,T_GR-SORT2,T_GR-SORT3,T_GR-SORT4,T_GR-SORT5,T_GR-TREND1,T_GR-TREND2,T_GR-TRAIL,T_GR-SCALE1,T_GR-SCALE2,T_GR-SCALE3,T_GR-SLOPE,T_GR-SCAT,T_GR-LINE,T_GR-COL,T_GR-BAR,T_GR-SIZE1,T_GR-SIZE2,T_GR-SEL,T_GR-YAXIS,T_GRAPH,T_TABLE,T_MAP-SYMB,T_OPTIONS,T_FULLSCR1,T_FULLSCR2,T_CAT,T_CAT2,T_REG,T_REG1,T_REG2,T_SEL-INDIC,T_BOOKM1,T_BOOKM2,T_SOURCE1,T_MAP-POPUP,T_SAVE1,T_PRTSCRN,T_MAP,T_MAP-TXT-S,T_MAP-SYMB-S,T_LEGEND-EST,T_LEGEND-VAL,T_MAP-CLR,T_MAP-BG-I,T_MAP-BG-O,T_MAP-BRDR,T_MAP-EXCL,T_MAP-ND,T_MAP-OVER,T_MAP-S,T_MAP-S-BRDR,T_MAP-TXT-C1,T_MAP-TXT-C2,T_LEGEND-CLR,T_SEQ,T_DIV,T_GR-AXIS,T_GR-BRDR-T,T_GR-GRID-T,T_GR-MARGIN,T_GR-TXT-S,T_GR-DOT-S,T_GR-A-TR,T_GR-CLR,T_GR-A-CLR,T_GR-BG-CLR,T_GR-BRDR-CLR,T_GR-P-BRDR-CLR,T_GR-BUL,T_GR-GRID-CLR,T_GEN,T_ANI-TIME,T_DECPLACE,T_ADJ,T_ADJ-I,T_ADJ-R,T_ADJ-Y,T_VALUES,T_SAVE,T_IMTYPE,T_IMSIZE,T_IMQUAL,T_SELECT,T_REMOVE,T_DESEL,T_REFRESH,T_HEADER,T_TABLE-C1,T_TABLE-C2,T_TABLE-S1,T_TABLE-S2,T_MEAN,T_PRCNTILE,T_STDEV,T_RANGE,T_TO,T_VIEW,T_VIEW0,T_VIEW1,T_VIEW2,T_VIEW3,T_VIEW4,T_VIEW5,T_VIEW6,T_VIEW7,T_VIEW8,T_VIEW9,T_VIEW10,T_VIEW11,T_VIEW12,T_VIEW13,T_VIEW14,T_VIEW15,T_VIEW16,T_CLOSE,T_EDIT,T_MLN,T_OPTIONS-M,T_ND,T_OK,T_TND,T_PLAY,T_STOP,T_YEAR,"+System.getProperty("line.separator"));
            out.write(",,,-,-,1,1,-,YO BT BA,-,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,2,1,1,12,1,100,20,10,30,TRUE,FALSE,0xBD0026,0xF03B20,0xFD8D3C,0xFECC5C,0xFFFFB2,no color,no color,no color,no color,0xFFFFFF,0xFFFFFF,0x333333,0xEEEEEE,0xCCCCCC,0xFFFF00,0x000000,0x67FC56,0x000000,0x000000,0xFFFFFF,100,auto,0.5,auto,auto,0.5,10,100,1,1,1,15,0xFD8D3C,0x000000,0xFFFFFF,0x333333,0x000000,0xCCCCCC,100,TRUE,TRUE,1,14,40,60,10,10,2,0x000000,0xFFFFFF,0xEEEEEE,0xEEEEEE,");
            out.write("0xFFFFFF,0x838383,0x6C6C6C,0x838383,0x6C6C6C,0xFFFFFF,0xFFFFC8,0xFFFF5B,0xC8FFC8,0x67FC56,0xFFFFFF,0xFFFFC6,0xFEEF6B,0xFEAD6B,0x01E450,0xB9CEFF,0x67FC56,0.8,1,TRUE,TRUE,FALSE,1,Pilihan grafik,Sortir ,Sortir,Rendah ke tinggi,Tinggi ke rendah,Tinggi ke rendah - dari tengah,Alfabetis,Tinggi ke rendah berdasarkan rata-rata,Tampilkan trendline,Sembunyikan trendline,Show trails,Ukuran bubble,berdasarkan indikator,Berukuran sama,Slope,Scatter plot,Grafik time series ,Grafik kolom,Grafik bar,Full screen ,Keluar full screen,Pilih provinsi,Pilih indikator untuk sumbu-y,Grafik,");
            out.write("Tabel data,Simbol,Setting,Full screen ,Tutup full screen,Kategori,Pilih kategori,Pilih region,Map,Custom region,Indikator,Bookmark indikator,Bookmark indikator yang dipilih,Sumber,Klik data provinsi,Tunggu�menyimpan gambar�,Untuk menyimpan gambar: <br><br>(i) tekan tombol 'PrtScrn' (Print Screen) pada keyboard; <br>(ii) buka aplikasi image viewer; misal Microsoft Paint kemudian 'Edit -> Paste'; <br>(iii) simpan gambar sesuai format yang dikehendaki.,Peta,Ukuran teks pada peta,Ukuran simbol,Legenda: estimasi penyebaran nilai terbaik,Legenda: menampilkan nilai maksimum & minimum,Warna peta,Background gradient 1,Background gradient 2,Borders,Map - Excluded,Map - No Data,Map - Mouse over,Warna simbol,Warna border simbol,Teks,Outline teks (stroke),Warna legenda,Barisan,Acak,Sumbu unit / interval,Ketebalan border,Ketebalan grid line,Ukuran margin,Ukuran teks grafik,Ukuran bublle,Transparan,Warna grafik,Area (bar / titik),Background,Border,Bubble border,Bullet grafik,Grid,General,Durasi animasi (detik),Decimal places,Skala grafik,saat mengubah indikator,saat mengubah region,saat mengubah tahun,Gunakan nilai,Simpan,Tipe gambar,Ukuran gambar,Kualitas gambar (JPEG only),Pilih provinsi,Hapus,Deselect semua,Refresh,Provinsi,Copy data tabel,Buka Ms. Excel kemudian - Edit -> Paste. ,Simpan data tabel,Format file .CSV ,Rata-rata,Persentase,Std. deviasi,Range,ke,Menu,Informasi provinsi,Singkatan,Kode ISO,Nama,Nilai,Popup,Grafik bar,Indikator,Nilai,Simbol,Jendela,Grafik,Indikator,Legenda,Setting,Pilih provinsi,Tabel,Keluar,Edit,Juta,Pilihan lanjutan,No data,OK,Ribu,Play dari awal,Stop,Tahun,"+System.getProperty("line.separator"));
            out.write(",,,,X,Y,ZOOM,COLOR,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
            out.write("Map,,,,531.3,349.2,52.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
            out.write(",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
            out.write("GLOBAL,,,,,,,,,,,,AC,SU,SB,RI,JA,SS,BE,LA,BB,KR,JK,JB,JT,YO,JI,BT,BA,NB,NT,KB,KT,KS,KI,SA,ST,SN,SG,GO,SR,MA,MU,PB,PA,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
            // DATA
            Calendar cal = Calendar.getInstance();
            int i = cal.get(Calendar.MONTH);
            String[] month = {"Januari","Februari","Maret","April","Mei","juni","juli","Agustus","September","oktober","November","Desember"};
            ArrayList<Penyakit> ap = this.getPenyakit();
            while(i>=0){
                boolean first = true;
                int d = cal.get(Calendar.DATE);
                while(d>0){
                    if(first){
                        out.write(","+month[i]+","+d+",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
                        first = false;
                    }else{
                        out.write(",,"+d+",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
                    }
                    Iterator itr = ap.iterator();
                    while (itr.hasNext()) {
                        Penyakit p = (Penyakit) itr.next();
                        //out.write(",,,"+p.getNama()+",,,Jiwa,0=[0x1A9641] 1=[0xA6D96A] 2=[0xFDAE61] 3=[0xD7191C] ,,,,,96.06,94.26,94.63,95.01,94.3,92.97,94.44,94.28,91.76,93.76,93.81,94.19,95.13,94.33,94.56,93.42,94.92,94.2,91.72,93.96,95.72,94.17,93.55,91.11,92.8,92.15,94.24,90.44,92.79,93.89,92.46,90.78,81.69,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
                        out.write(",,,"+p.getNama()+",,,Jiwa,0=[0x1A9641] 1=[0xA6D96A] 2=[0xFDAE61] 3=[0xD7191C] ,,,,,");
                        String buff="";
                        Random randomGenerator = new Random();
                        int randomInt;
                        for(int x=0;x<33;x++){
                            randomInt = randomGenerator.nextInt(10000);
                            buff += randomInt+",";
                        }
                        out.write(buff);
                        out.write(",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"+System.getProperty("line.separator"));
                    }
                    cal.add(Calendar.DATE, -1);
                    d--;
                }
                i--;
            }
            out.flush();
            out.close();
        } catch (FileNotFoundException ex) {
            System.out.println(ex.toString());
        } catch (java.io.IOException e){
            System.out.println(e.toString());
        }
    }
    
    public ArrayList<Penyakit> getPenyakit() {
        ArrayList<Penyakit> ap = new ArrayList<Penyakit>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM penyakit";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Penyakit p = new Penyakit(rs.getInt("id"), rs.getString("nama"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
}
